﻿<cfsilent>
	<cfscript>
		
		stringAdvice = getProperty("serviceFactory").getBean("stringAdvice");
		dateTimeAdvice = getProperty("serviceFactory").getBean("datetimeAdvice");
		mathAdvice = getProperty("serviceFactory").getBean("senateMathAdvice");
		
		temFile = GetTempDirectory() & createUUID() & ".xls";
		downFile = URLEncodedFormat("调查问卷结果导出" , "utf-8");
		
		/* 生成电子表格 */
		excel = getProperty("serviceFactory").getBean("spreadSheetObject");
		
		postQSNID = event.getArg("QSNIDs");
		
		questArray = [];
		
		if ( len(postQSNID) ) {
			
			row = 0;
			
			qsnIds = listToArray( postQSNID, "," );
			
			questHSQL = "and ( ";
			
			for ( qsnId in qsnIds ) {
				
				row++;
				
				questHSQL = questHSQL & " a.qsn_id = '" & qsnId & "'";
				
				if ( row lt arrayLen(qsnIds) ) {
					questHSQL = questHSQL & " or ";
				}
			}
			
			questHSQL = questHSQL & ") ";

		
			/* 调查问卷列表 */
			sql = "SELECT 
						 a.qsn_id, 
						 a.title, 
						 a.date_start, 
						 a.date_end, 
						 t_teacher.tch_name, 
						 (SELECT COUNT(*) FROM t_respondents b WHERE b.qsn_id = a.qsn_id AND b.state = 'S') countRes_S, 
						 (SELECT COUNT(*) FROM t_respondents c WHERE c.qsn_id = a.qsn_id) countRes  
					FROM 
						 t_questionnaire a 
						 INNER JOIN t_teacher ON t_teacher.tch_id = a.tch_id 
					WHERE
						 a.state = '2' " & questHSQL & "
					ORDER BY 
						 a.date_end DESC, a.title "; 
			
			queryObj = new Query( datasource=application.dnsSlave );
			
			rs_quest = queryObj.execute( sql=sql ).getResult();
		
				
		}

		/* 创建工作簿 */
		spreadsheetObj = excel.SpreadSheetNew("调查问卷", false);
		
		/* 添加表头 */
		excel.SpreadsheetAddrow(spreadsheetObj, "问卷,开始时间,结束时间,发布人,有效回执,预计回执");
		
		row = 1;
		
		for ( i=1; i LTE rs_quest.recordCount; i++ ){	
			row++;
			
			/* 构建问卷数据 */
			rowData = rs_quest["title"][i]
					& "," & dateTimeAdvice.formatDateString( rs_quest["date_start"][i], "YYYY年MM月DD日" )
					& "," & dateTimeAdvice.formatDateString( rs_quest["date_end"][i], "YYYY年MM月DD日" )
					& "," & rs_quest["tch_name"][i]
					& "," & rs_quest["countRes_S"][i]
					& "," & rs_quest["countRes"][i]
					;
			
			/* 写入问卷信息 */
			excel.SpreadsheetAddrow(spreadsheetObj, rowData);
			
		}
		
		excel.SpreadsheetCreateSheet(spreadsheetObj, "指标评分明细");
		excel.SpreadsheetSetActiveSheet(spreadsheetObj, "指标评分明细");
		
		/* 添加表头 */
		excel.SpreadsheetAddrow(spreadsheetObj, "问卷,指标,得分,子项1,子项2,子项3,子项4,子项5");
		
		row = 1;
		
		for ( i=1; i LTE rs_quest.recordCount; i++ ){	
			
			row++;
			
			sql = "SELECT 
						 a.quo_id, 
						 a.question, 
						 a.measure, 
						 (SELECT COUNT(*) FROM t_vote_response b WHERE b.quo_id = a.quo_id AND b.answer = 'E') countRes_E, 
						 (SELECT COUNT(*) FROM t_vote_response b WHERE b.quo_id = a.quo_id AND b.answer = 'G') countRes_G, 
						 (SELECT COUNT(*) FROM t_vote_response b WHERE b.quo_id = a.quo_id AND b.answer = 'S') countRes_S, 
						 (SELECT COUNT(*) FROM t_vote_response b WHERE b.quo_id = a.quo_id AND b.answer = 'Y') countRes_Y, 
						 (SELECT COUNT(*) FROM t_vote_response b WHERE b.quo_id = a.quo_id AND b.answer = 'N') countRes_N 
					FROM 
						 t_quota a 
					WHERE 
						 a.qsn_id = :sheetId 
					ORDER BY 
						 a.quo_idx "; 
			
			queryObj = new Query( datasource=application.dnsSlave );
			queryObj.addParam( name="sheetId", value=rs_quest["qsn_id"][i], cfsqltype="cf_sql_char" );
			
			rs_quota = queryObj.execute( sql=sql ).getResult();
		
		
			for ( j=1; j LTE rs_quota.recordCount; j++){
				
				switch ( rs_quota["measure"][j] ) {
					
					case "L1":
						/* 7-Y 8-N */
						summarySet = [ rs_quota["countRes_Y"][j], rs_quota["countRes_N"][j] ];
						break;
						
					case "L3":
						/* 6-S 7-Y 8-N */
						summarySet = [ rs_quota["countRes_S"][j], rs_quota["countRes_Y"][j], rs_quota["countRes_N"][j] ];
						break;
						
					case "L4":
						/* 4-E 5-G 7-Y 8-N */
						summarySet = [ rs_quota["countRes_E"][j], rs_quota["countRes_G"][j], rs_quota["countRes_Y"][j], rs_quota["countRes_N"][j] ];
						break;
					
				}
				
				/* 构建问卷结果数据 */
				rowData = rs_quota["question"][j] 
							& "," & rs_quota["question"][j]
							& "," & numberFormat( 100 * mathAdvice.getSatisfactionDegree( summarySet ), "__.____" ); 
				
				/* 写入调查子项计数 */
				switch ( rs_quota["measure"][j] ) {
					
					case "L1":
						/* 7-Y 8-N */
						rowData = rowData & "," & rs_quota["countRes_Y"][j] & "," & rs_quota["countRes_N"][j];
						break;
						
					case "L3":
						/* 6-S 7-Y 8-N */
						rowData = rowData & "," & rs_quota["countRes_S"][j] & "," & rs_quota["countRes_Y"][j] & "," & rs_quota["countRes_N"][j];
						break;
						
					case "L4":
						/* 4-E 5-G 7-Y 8-N */
						rowData = rowData & "," & rs_quota["countRes_E"][j] & "," & rs_quota["countRes_G"][j] & "," & rs_quota["countRes_Y"][j] & "," & rs_quota["countRes_N"][j];
						break;
					
				}
				
				/* 写入问卷结果数据 */
				excel.SpreadsheetAddrow(spreadsheetObj, rowData);
				
			}

		}
		
		excel.SpreadsheetSetActiveSheet(spreadsheetObj, "调查问卷");
		
		excel.SpreadSheetWrite(spreadsheetObj, temFile, true);
		
	</cfscript>

	<cfheader name="Content-Disposition" value="attachment; filename=#downFile#.xls" />
	<cfcontent file="#temFile#" reset="yes" type="application/msexcel" deletefile="yes" />	

</cfsilent>